import os
from datetime import datetime,timedelta

from utils.DBService import sqlquery

BANHEZHAN_SQL = """
WITH Results AS (
    SELECT
        subquery.departname,
        SUM(CASE
                WHEN CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) >= '{}'
                    AND CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) < '{}' THEN 1
                ELSE 0
            END) AS jidaoqi,
        ROUND(
            100.0 * SUM(CASE
                    WHEN CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) >= '{}'
                        AND CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) < '{}' THEN 1
                    ELSE 0
                END) / NULLIF(SUM(CASE
                        WHEN CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) >= '{}'
                            AND CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) <= '{}' THEN 1
                        ELSE 0
                    END), 0), 2) AS jijianglv,
        SUM(CASE
                WHEN CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) = '{}' THEN 1
                ELSE 0
            END) AS yidaoqi,
        ROUND(
            100.0 * SUM(CASE
                    WHEN CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) = '{}' THEN 1
                    ELSE 0
                END) / NULLIF(SUM(CASE
                        WHEN CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) >= '{}'
                            AND CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) <= '{}' THEN 1
                        ELSE 0
                    END), 0), 2) AS yidaoqilv,
        SUM(CASE
                WHEN CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) > '{}' 
                                    AND CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) <= '{}' THEN 1
                ELSE 0
            END) AS yichaoqi,
        ROUND(
            100.0 * SUM(CASE
                    WHEN CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) > '{}' 
                                            AND CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) <= '{}' THEN 1
                    ELSE 0
                END) / NULLIF(SUM(CASE
                        WHEN CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) >= '{}'
                            AND CONVERT(nvarchar(10), DATEADD(DAY, q.LQ, q.ZZRQ), 120) <= '{}' THEN 1
                        ELSE 0
                    END), 0), 2) AS yichaoqilv
    FROM
        T_SYJZB AS q
    JOIN (
        SELECT
            b.gprsbianhao,
            c.departname
        FROM
            t_s_depart AS c
        JOIN Banhezhanxinxi AS b ON c.id = b.departid
        WHERE
            b.orgcode LIKE (SELECT org_code FROM t_s_depart WHERE id = '{}') + '%'
            AND b.gprstype NOT IN (1, 2, 5)
    ) AS subquery ON q.SBBH = subquery.gprsbianhao
    GROUP BY
        subquery.departname
)
SELECT departname, '即将到期' AS status, jidaoqi, jijianglv
FROM Results

UNION ALL

SELECT departname, '已到期' AS status, yidaoqi, yidaoqilv
FROM Results

UNION ALL

SELECT departname, '已超期' AS status, yichaoqi, yichaoqilv
FROM Results;"""

def lingqi_ovStatic(Args,user):
    print('Quest参数:\n\t',Args)
    depId = Args['depId']
    dates = Args['dates']
    depId = '\',\''.join(depId.split(','))

    if depId == '8a8ab0b246dc81120146dc8180ba0017'or \
            depId == 'f632d53969b410ac0169bd10699f0044'or \
            depId == 'All':
        depId = 'f632d53969b410ac0169bd10699f0044'
    elif depId == 'f6323b406b4fab1b016bacabdb9f01d3':
        depId = 'f6323b406b4fab1b016bacabdb9f01d3'
    elif depId == 'f6323b406b4fab1b016bacac674901d5':
        depId = 'f6323b406b4fab1b016bacac674901d5'
    elif depId == 'f6323b406d19fc87016d1a5c43910016':
        depId = 'f6323b406d19fc87016d1a5c43910016'
    elif depId == 'f6323b406d19fc87016d1a5b861b0014':
        depId = 'f6323b406d19fc87016d1a5b861b0014'
    else:
        depId = 'f632d53969b410ac0169bd10699f0044'

    date_obj = datetime.strptime(dates, "%Y-%m-%d").date()
    date_two = date_obj - timedelta(days=2)
    date_seven = date_obj + timedelta(days=7)

    banhezhanSql = BANHEZHAN_SQL.format(date_two,dates,date_two,dates,date_two,date_seven,dates,dates,date_two,
                                        date_seven,dates,date_seven,dates,date_seven,date_two,date_seven,depId)
    print('SQL:\n\t', banhezhanSql)
    banhezhanData = sqlquery(BANHEZHAN_SQL.format(date_two,dates,date_two,dates,date_two,date_seven,dates,dates,
                                                  date_two,date_seven,dates,date_seven,dates,date_seven,date_two,date_seven,depId))

    slData = []
    slData = [process_data(data) for data in banhezhanData]
    print('Data:\n\t', slData)

    resdata = []
    if slData.__len__() == 0:
        marktitle = '当前时间没有数据'
        return resdata,slData,marktitle
    else:
        marktitle = str(date_two.year) + '年' + str(date_two.month) + '月' + str(date_two.day) + '日' + '到' + str(
            date_seven.year) + '年' + str(date_seven.month) + '月' + str(date_seven.day) + '日' + '试验龄期情况如下:'

    children = []
    a_list = []
    b_list = []
    c_list = []
    d_list = []
    for index, item in enumerate(slData):
        station_name = item[0]
        a = item[0:]
        data = {
            'key': index,
            'departname': a[0],
            'cjpanshu': a[1],
            'cjichaobL': a[2],
            'zjpanshu': float(a[3])
        }
        table_head = [
            {
                "title": '状态',
                "dataIndex": list(data.keys())[2],
                "key": list(data.keys())[2],
            },
            {
                "title": '组数',
                "dataIndex": list(data.keys())[3],
                "key": list(data.keys())[3],
            },
            {
                "title": '占比(%)',
                "dataIndex": list(data.keys())[4],
                "key": list(data.keys())[4],
            }]
        if station_name.startswith("武黄1标"):
            a_list.append(data)
        elif station_name.startswith("武黄2标") :
            b_list.append(data)
        elif station_name.startswith("武黄3标") :
            c_list.append(data)
        else:
            d_list.append(data)
    if len(a_list)>0:
        add_totals(a_list)
        childrenA_Data = {"children":[{"title": '武黄1标试验室', "data": {"tableHead": table_head, "tableBody": a_list , 'echarts' : pro_data(a_list)}}] ,"title": '武黄1标'}
    children.append(childrenA_Data)
    print(a_list,"------------------------------------")
    if len(b_list)>0:
        add_totals(b_list)
        childrenB_Data = {"children":[{"title": '武黄2标试验室', "data": {"tableHead": table_head, "tableBody": b_list , 'echarts' : pro_data(b_list)}}] ,"title": '武黄2标'}
        children.append(childrenB_Data)
    print(b_list,"------------------------------------")
    if len(c_list)>0:
        add_totals(c_list)
        childrenB_Data = {"children":[{"title": '武黄3标试验室', "data": {"tableHead": table_head, "tableBody": c_list , 'echarts' : pro_data(c_list)}}] ,"title": '武黄3标'}
        children.append(childrenB_Data)
    if len(d_list)>0:
        add_totals(d_list)
        childrenB_Data = {"children":[{"title": '武黄高速监理', "data": {"tableHead": table_head, "tableBody": d_list , 'echarts' : pro_data(d_list)}}] ,"title": '武黄高速监理'}
        children.append(childrenB_Data)
    result = [{"title": "武黄高速", "children": children}]
    return slData,result,marktitle

def add_totals(data):
    # 计算合计值
    total_cjichaobL = sum(item["cjichaobL"] for item in data)
    total_zjpanshu = sum(item["zjpanshu"] for item in data)

    # 添加合计项
    total_item = {
        "cjichaobL": total_cjichaobL,
        "cjpanshu": "合计",
        "departname": "",
        "key": "",
        "zjpanshu": total_zjpanshu
    }

    data.append(total_item)

    return data

def pro_data(data):
    ydata = []
    for item in data:
        if item['cjpanshu'].startswith("即将到"):
            a = item['cjichaobL']
            ydata.append({
                'value': item['zjpanshu'],
                'name': str(item['cjpanshu']),
                "realValue":item['cjichaobL']
            })
            continue
        if item['cjpanshu'].startswith("已到"):
            b = item['cjichaobL']
            ydata.append({
                'value': item['zjpanshu'],
                'name': str(item['cjpanshu']),
                "realValue":item['cjichaobL']
            })
            continue
        if item['cjpanshu'].startswith("已超"):
            c = item['cjichaobL']
            ydata.append({
                'value': item['zjpanshu'],
                'name': str(item['cjpanshu']),
                "realValue":item['cjichaobL']
            })
            continue
    option = {
        "tooltip": {
            "trigger": 'item',
            "formatter": "{a} <br/>{b}: {c}%"
        },
        "graphic": [
            {
                "type": "text",
                "left": "center",
                "top": "center",
                "style": {
                    "text": str(a+b+c)+" 组",
                    "textAlign": "center",
                    "fill": "#000",
                    "fontSize": 24
                }
            }
        ],
        "legend": {
            "top": '1%',
            "left": 'center'
        },
        "series": [
            {
                "name": item['departname'],
                "type": 'pie',
                "radius": ['40%', '75%'],
                "avoidLabelOverlap": "false",
                "label": {
                    "show": "true",
                    "position": "inside",  # 将 label 显示在扇形区域内部
                    "formatter": "{c}%",   # 显示百分比
                    "color": "#333333",
                    "fontSize": 8
                },
                "emphasis": {
                    "label": {
                        "show": "true",
                        "fontSize": 20,
                        "fontWeight": 'bold'
                    }
                },
                "labelLine": {
                    "show": "false"
                },
                'data': ydata
            }
        ]
    };
    return option

def process_data(data):
    if os.environ.get("ENV_NAME") == '1':
        return [ data[0],data[1],
                 data[2] if data[2] is not None else 0.0,
                 data[3] if data[3] is not None else 0.0]
    else:
        return [data[0],data[1].encode('latin1').decode('gbk'),
                data[2] if data[2] is not None else 0.0,
                data[3] if data[3] is not None else 0.0]

functionConf = {
    "name": "lingqi_ovStatic",
    "description": "key point:试验龄期提醒。Check the trial status of each project site by ID and time",
    "parameters": {
        "type": "object",
        "properties": {
            "depId": {
                "type": "string",
                "description": "Department IDs. 武黄1标是(f6323b406b4fab1b016bacabdb9f01d3).武黄2标是(f6323b406b4fab1b016bacac674901d5).问武黄传参数:武黄高速"
            },
            "dates": {
                "type": "string",
                "description": "Date(s) of material production in yyyy-MM-dd format. Use comma-separated values (e.g., '2023-07-14,2023-07-15')."
            }
        },
        "required": [
            "depId",
            "dates"
        ]
    }
}